﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;

namespace BLL
{
   public class ClsChiTietBomTNBus
    {
       public ClsChiTietBomTNInfo Info = new ClsChiTietBomTNInfo();
        public DataTable SelectOne()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT TTCHUNG.TenGK,BomThiNghiem.MaBomTN,ChiTietBomTN.SoHieuDotBom,ChiTietBomTN.ChieuSauGKTruocBom,ChiTietBomTN.ChieuSauGKSauBom,ChiTietBomTN.ChieuDayTangChuaNuoc,ChiTietBomTN.CSDoanBatDauBTN,ChiTietBomTN.CSDoanKetThucBTN,ChiTietBomTN.SoGioBom,ChiTietBomTN.SoGioOnDinh,ChiTietBomTN.SoGioPhucHoi,ChiTietBomTN.MucNuocTinh,ChiTietBomTN.MucNuocDong,ChiTietBomTN.MucNuocHaThap,ChiTietBomTN.LuuLuongNuoc,ChiTietBomTN.NhietDoNuoc,ChiTietBomTN.NhietDoKhongKhi,ChiTietBomTN.GhiChu
                              FROM TTCHUNG,BomThiNghiem,ChiTietBomTN
                              WHERE TTCHUNG.MaGK= BomThiNghiem.MaGK 
                              AND BomThiNghiem.MaBomTN=ChiTietBomTN.MaBomTN
                              AND TTCHUNG.MaGK like'%" + Info.MaGK + "%' AND BomThiNghiem.MaBomTN like '%" + Info.MaBomTN + "%'AND TTCHUNG.Tinh LIKE '" + Info.MaTinh + "'";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else
            {
                return null;
            }
        }

        public DataTable SelectAll()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT TTCHUNG.TenGK,BomThiNghiem.MaBomTN,ChiTietBomTN.SoHieuDotBom,ChiTietBomTN.ChieuSauGKTruocBom,ChiTietBomTN.ChieuSauGKSauBom,ChiTietBomTN.ChieuDayTangChuaNuoc,ChiTietBomTN.CSDoanBatDauBTN, ChiTietBomTN.CSDoanKetThucBTN,ChiTietBomTN.SoGioBom,ChiTietBomTN.SoGioOnDinh,ChiTietBomTN.SoGioPhucHoi,ChiTietBomTN.MucNuocTinh,ChiTietBomTN.MucNuocDong,ChiTietBomTN.MucNuocHaThap,ChiTietBomTN.LuuLuongNuoc,ChiTietBomTN.NhietDoNuoc,ChiTietBomTN.NhietDoKhongKhi,ChiTietBomTN.GhiChu
                              FROM TTCHUNG,BomThiNghiem,ChiTietBomTN
                              WHERE TTCHUNG.MaGK= BomThiNghiem.MaGK 
                              AND BomThiNghiem.MaBomTN=ChiTietBomTN.MaBomTN
                              AND TTCHUNG.Tinh LIKE '" + Info.MaTinh + "'";
            //string strSql = @"SELECT * From ThachHoc";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public bool Them()
        {
            string strSql = "EXECUTE InsertChiTietBomTN'" + Info.MaBomTN + "','" + Info.SoHieuDotBom + "','" + Info.ChieuSauGKTruocBom + "','" + Info.ChieuSauGKSauBom + "','" + Info.ChieuDayTangChuaNuoc + "','" + Info.CSDoanBatDauBTN + "','" + Info.CSDoanKetThucBTN + "','" + Info.SoGioBom + "','" + Info.SoGioOnDinh + "','" + Info.SoGioPhucHoi + "','" + Info.MucNuocTinh + "','" + Info.MucNuocDong + "','" + Info.MucNuocHaThap + "','" + Info.LuuLuongNuoc + "','" + Info.NhietDoNuoc + "','" + Info.NhietDoKhongKhi + "',N'" + Info.GhiChu + "'";

            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }

        public bool Sua()
        {
            string strSql = "EXECUTE UpdateChiTietBomTN'" + Info.MaBomTN + "','" + Info.SoHieuDotBom + "','" + Info.ChieuSauGKTruocBom + "','" + Info.ChieuSauGKSauBom + "','" + Info.ChieuDayTangChuaNuoc + "','" + Info.CSDoanBatDauBTN + "','" + Info.CSDoanKetThucBTN + "','" + Info.SoGioBom + "','" + Info.SoGioOnDinh + "','" + Info.SoGioPhucHoi + "','" + Info.MucNuocTinh + "','" + Info.MucNuocDong + "','" + Info.MucNuocHaThap + "','" + Info.LuuLuongNuoc + "','" + Info.NhietDoNuoc + "','" + Info.NhietDoKhongKhi + "',N'" + Info.GhiChu + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }
        public bool xoa()
        {
            string strSql = "EXECUTE DeleteChiTietBomTN'" + Info.MaBomTN + "','" + Info.SoHieuDotBom + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }
    }
}
